﻿Imports TNCDAL
Imports TNCBAL
Imports System.Xml

Public Class frmXacNhanPhieu
    Inherits DevComponents.DotNetBar.Office2007RibbonForm

    Dim db As New TanNamChinhDataContext
    Dim m_ID_User As Integer = GeneralClassBAL._User.ID_User

    Private Sub frmXacNhanPhieu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ExpandablePanel1.Height = Me.Height / 4
        ExpandablePanel2.Height = Me.Height / 4
        ExpandablePanel3.Height = Me.Height / 4
        ''Xem lại lọc trước khi load cho ít file
        'txtFrom.Text = 300
        'txtTo.Text = 310
        'KhoiTaoFile(300, 310, True)
    End Sub

    Public Sub KhoiTaoFile(ByVal FromNo As Integer, ByVal ToNo As Integer, ByVal isExport As Boolean)
        'Dim dtFile As DataTable
        'dtFile = KhoiTaodtFile(FromNo, ToNo, isExport)

        Dim dc As New TanNamChinhDataContext
        Dim q = dc.sp_Sy_ViewFile(FromNo, ToNo, isExport)


        Dim source As New BindingSource
        source.DataSource = q
        BindingNavigatorFile.BindingSource = source
        dtgFile.DataSource = source

        FormatdtgFile()
    End Sub

    Public Function KhoiTaodtFile(ByVal FromNo As Integer, ByVal ToNo As Integer, ByVal isExport As Boolean) As DataTable
        Dim dt As New DataTable
        Dim filelist = From p In db.tblFiles
                       Where p.FileNo >= FromNo And p.FileNo <= ToNo And p.IsExport = isExport
                    Select p.ID_File, FileNo = p.FileNo.ToString + IIf(p.IsExport, "/EX", "/IM"), p.CreatedDate, p.tblCustomer.CustomerName, p.Ratio


        dt.Columns.Add("ID_File")
        dt.Columns.Add("FileNo")

        Dim colCreatedDate As DataColumn
        colCreatedDate = New DataColumn()
        colCreatedDate.DataType = System.Type.GetType("System.DateTime")
        colCreatedDate.ColumnName = "CreatedDate"
        dt.Columns.Add(colCreatedDate)

        dt.Columns.Add("CustomerName")

        Dim colBuying As DataColumn
        colBuying = New DataColumn()
        colBuying.DataType = System.Type.GetType("System.Double")
        colBuying.ColumnName = "Buying"
        dt.Columns.Add(colBuying)

        For Each file In filelist
            Dim ID_File As Integer = file.ID_File

            Dim row As DataRow
            row = dt.NewRow
            row("ID_File") = file.ID_File
            row("FileNo") = file.FileNo
            row("CreatedDate") = file.CreatedDate
            row("CustomerName") = file.CustomerName
            'Lấy giá mua theo file
            Dim Buying As Double = 0

            'Dim BuyingFee = From p In db.tblFileFees
            '                  Where p.ID_File = ID_File
            '                  Select p

            'For Each fee In BuyingFee
            '    If fee.ID_Currency > 1 Then
            '        If Not file.Ratio Is Nothing Then
            '            Buying += file.Ratio * fee.Price
            '        End If
            '    ElseIf fee.ID_Currency = 1 Then
            '        Buying += fee.Price
            '    End If
            'Next

            row("Buying") = Buying
            dt.Rows.Add(row)
        Next

        Return dt
    End Function

    Public Sub FormatdtgFile()
        dtgFile.RowHeadersWidth = 20
        dtgFile.RowHeadersDefaultCellStyle.ForeColor = Color.Blue
        'dtgFile.RowTemplate.Height = 25
        dtgFile.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        dtgFile.MultiSelect = False
        'dtgFile.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        dtgFile.ReadOnly = True
        dtgFile.BackgroundColor = Color.White
        dtgFile.AllowUserToAddRows = False
        dtgFile.AllowUserToDeleteRows = False

        dtgFile.Columns("ID_File").Visible = False
        dtgFile.Columns("IsExport").Visible = False

        With dtgFile.Columns("FileNo")
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With

        With dtgFile.Columns("CreatedDate")
            .Width = 70
            .DefaultCellStyle.Format = "dd/MM/yyyy"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
        With dtgFile.Columns("TotalBuying")
            .HeaderText = "Buying"
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .DefaultCellStyle.BackColor = Color.Azure
        End With
        With dtgFile.Columns("TotalPayment")
            .HeaderText = "Payment"
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .DefaultCellStyle.BackColor = Color.Azure
        End With
        With dtgFile.Columns("TotalSelling")
            .HeaderText = "Selling"
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .DefaultCellStyle.BackColor = Color.LightYellow
        End With
        With dtgFile.Columns("TotalReceipt")
            .HeaderText = "Receipt Cus"
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .DefaultCellStyle.BackColor = Color.LightYellow
        End With
        With dtgFile.Columns("TotalAdvance")
            .HeaderText = "Advance"
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .DefaultCellStyle.BackColor = Color.Azure
        End With
        With dtgFile.Columns("TotalReceiptAdvance")
            .HeaderText = "Receipt Adv"
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            .DefaultCellStyle.BackColor = Color.Azure
        End With
        

        'With dtgFile.Columns("TOTAL")
        '    .DefaultCellStyle.BackColor = Color.Azure
        '    .DefaultCellStyle.Font = f
        'End With

    End Sub

    Public Sub KhoiTaoHBL(ByVal ID_File As Integer)
        Try
            Dim dtHBL As DataTable
            dtHBL = KhoiTaodtHBL(ID_File)

            Dim source As New BindingSource
            source.DataSource = dtHBL
            BindingNavigatorHBL.BindingSource = source
            dtgHBL.DataSource = source

            FormatdtgHBL()
        Catch ex As Exception

        End Try
    End Sub

    Public Sub FormatdtgHBL()
        dtgHBL.RowHeadersWidth = 20
        dtgHBL.RowHeadersDefaultCellStyle.ForeColor = Color.Blue
        'dtgFile.RowTemplate.Height = 25
        'dtgHBL.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        dtgHBL.MultiSelect = False
        'dtgHBL.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        dtgHBL.ReadOnly = True
        dtgHBL.BackgroundColor = Color.White
        dtgHBL.AllowUserToAddRows = False
        dtgHBL.AllowUserToDeleteRows = False

        dtgHBL.Columns("ID_HBL").Visible = False

        With dtgHBL.Columns("HBLNo")
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Width = 120
        End With

        With dtgHBL.Columns("Selling")
            .HeaderText = "Total Selling"
            .Width = 150
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        End With
    End Sub

    Public Function KhoiTaodtHBL(ByVal ID_File As Integer) As DataTable
        Dim HBLlist = From p In db.tblHBLs
                  Where p.tblFileMBL.ID_File = ID_File
                  Select p.ID_HBL, p.HBLNo, p.Ratio

        Dim dt As New DataTable
        dt.Columns.Add("ID_HBL")
        dt.Columns.Add("HBLNo")

        Dim colSelling As DataColumn
        colSelling = New DataColumn()
        colSelling.DataType = System.Type.GetType("System.Double")
        colSelling.ColumnName = "Selling"
        dt.Columns.Add(colSelling)

        For Each HBL In HBLlist
            Dim row As DataRow
            row = dt.NewRow
            row("ID_HBL") = HBL.ID_HBL
            row("HBLNo") = HBL.HBLNo

            Dim m_ID_HBL = HBL.ID_HBL
            'Lấy giá bán theo HBL
            'Dim Selling As Double = 0

            'Dim SellingFee = From p In db.tblSellingFees
            '                  Where p.ID_HBL = ID_HBL
            '                  Select p

            'For Each fee In SellingFee
            '    If fee.ID_Currency > 1 Then
            '        If Not HBL.Ratio Is Nothing Then
            '            Selling += HBL.Ratio * fee.Price
            '        End If
            '    ElseIf fee.ID_Currency = 1 Then
            '        Selling += fee.Price
            '    End If
            'Next

            'row("Selling") = Selling
            Dim SellingFee = From p In db.tblSellingFees
                    Where p.ID_HBL = m_ID_HBL And p.Price > 0 And p.tblFee.IsActive = True
                    Order By p.tblFee.Rank
                    Select p.ID_HBL, p.ID_Fee, p.tblFee.FeeName, p.UnitPrice, p.Quantity, p.Price, p.ID_Currency, p.tblCurrency.Code, p.Description,
                    Ratio = (From q In db.tblHBLExchanges Where q.ID_HBL = m_ID_HBL And q.ID_Currency = p.ID_Currency Select q.Ratio).SingleOrDefault()

            Dim TongTien As Double = 0
            For Each sf In SellingFee
                Dim ID_Currency As Integer = 1
                ID_Currency = sf.ID_Currency
                If ID_Currency = 1 Then
                    TongTien += sf.Price
                Else
                    TongTien += sf.Price * sf.Ratio
                End If
            Next
            row("Selling") = TongTien
            dt.Rows.Add(row)
        Next

        Return dt
    End Function

    Private Sub dtgFile_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtgFile.CurrentCellChanged
        Try
            Dim ID_File As Integer
            ID_File = dtgFile.CurrentRow.Cells("ID_File").Value

            '1. Khoi tao HBL
            KhoiTaoHBL(ID_File)
            '2. Khoi tao AdvanceVoucher
            KhoiTaoAdvanceVoucher(ID_File)
            '3. Khoi tao ReceiptVoucher
            KhoiTaoReceiptVoucher(ID_File)
            '4. Khoi tao PaymentVoucher
            KhoiTaoPaymentVoucher(ID_File)
        Catch ex As Exception

        End Try
    End Sub

    Public Sub KhoiTaoAdvanceVoucher(ByVal ID_File As Integer)
        Try
            Dim listID_AV = (From p In db.tblFileFees
                     Where p.ID_File = ID_File
                     Select p.ID_AdvanceVoucher).Distinct.ToList

            Dim listAV = From p In db.tblAdvanceVouchers
                         Where listID_AV.Contains(p.ID_AdvanceVoucher)
                         Select p.ID_AdvanceVoucher, p.AdvanceVoucherNo, p.CreatedDate, p.Amount, p.Description, p.tblUser1.FullName, p.IsApprove, p.ID_ApproveUser, p.ID_ReceiptVoucher

            Dim source As New BindingSource
            source.DataSource = listAV
            BindingNavigatorAdvance.BindingSource = source
            dtgAdvanceVoucher.DataSource = source

            FormatdtgAdvanceVoucher()
        Catch ex As Exception

        End Try
    End Sub

    Public Sub FormatdtgAdvanceVoucher()
        dtgAdvanceVoucher.RowHeadersWidth = 20
        dtgAdvanceVoucher.RowHeadersDefaultCellStyle.ForeColor = Color.Blue
        'dtgAdvanceVoucher.RowTemplate.Height = 25
        'dtgAdvanceVoucher.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        dtgAdvanceVoucher.MultiSelect = False
        'dtgAdvanceVoucher.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        dtgAdvanceVoucher.ReadOnly = True
        dtgAdvanceVoucher.BackgroundColor = Color.White
        dtgAdvanceVoucher.AllowUserToAddRows = False
        dtgAdvanceVoucher.AllowUserToDeleteRows = False

        dtgAdvanceVoucher.Columns("ID_AdvanceVoucher").Visible = False
        dtgAdvanceVoucher.Columns("IsApprove").Visible = False
        dtgAdvanceVoucher.Columns("ID_ApproveUser").Visible = False
        dtgAdvanceVoucher.Columns("ID_ReceiptVoucher").Visible = False

        With dtgAdvanceVoucher.Columns("AdvanceVoucherNo")
            .HeaderText = "SốTU"
            .Width = 50
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
        With dtgAdvanceVoucher.Columns("CreatedDate")
            .HeaderText = "Ngày"
            .Width = 90
            .DefaultCellStyle.Format = "dd/MM/yyyy"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
        With dtgAdvanceVoucher.Columns("Amount")
            .HeaderText = "Số tiền"
            .Width = 100
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        End With
        With dtgAdvanceVoucher.Columns("Description")
            .HeaderText = "Nội dung tạm ứng"
            .Width = 270
        End With
        With dtgAdvanceVoucher.Columns("FullName")
            .HeaderText = "Người tạm ứng"
            .Width = 140
        End With
    End Sub

    Public Sub KhoiTaoReceiptVoucher(ByVal ID_File As Integer)
        'Khoi tao danh sach phieu thu theo hbl cua file va phieu thu tam ung cua file
        Dim listID_RV As New ArrayList

        Dim listID_AV = (From p In db.tblFileFees
                     Where p.ID_File = ID_File
                     Select p.ID_AdvanceVoucher).Distinct.ToList

        Dim listRV_AV = (From p In db.tblAdvanceVouchers
                        Where listID_AV.Contains(p.ID_AdvanceVoucher) And p.ID_ReceiptVoucher IsNot Nothing
                        Select p.ID_ReceiptVoucher).Distinct.ToList

        Dim HBLlist = (From p In db.tblHBLs
                  Where p.tblFileMBL.ID_File = ID_File
                  Select p.ID_HBL).ToList

        Dim listRV_HBL = (From p In db.tblSellingFees
                     Where HBLlist.Contains(p.ID_HBL) And p.ID_ReceiptVoucher IsNot Nothing
                     Select p.ID_ReceiptVoucher).Distinct.ToList

        Dim listRV = From p In db.tblReceiptVouchers
                     Where listRV_AV.Contains(p.ID_ReceiptVoucher) Or listRV_HBL.Contains(p.ID_ReceiptVoucher)
                     Select p.ID_ReceiptVoucher, ReceiptVoucherNo = p.ReceiptVoucherNo + "/PT", p.CreatedDate, p.Amount, p.Description, p.PayDay, p.Payee, p.IsApprove, p.ID_ApproveUser

        Dim source As New BindingSource
        source.DataSource = listRV
        BindingNavigatorReceipt.BindingSource = source
        dtgReceiptVoucher.DataSource = source

        FormatdtgReceiptVoucher()
    End Sub

    Public Sub FormatdtgReceiptVoucher()
        dtgReceiptVoucher.RowHeadersWidth = 20
        dtgReceiptVoucher.RowHeadersDefaultCellStyle.ForeColor = Color.Blue
        'dtgReceiptVoucher.RowTemplate.Height = 25
        'dtgReceiptVoucher.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        dtgReceiptVoucher.MultiSelect = False
        'dtgReceiptVoucher.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        dtgReceiptVoucher.ReadOnly = True
        dtgReceiptVoucher.BackgroundColor = Color.White
        dtgReceiptVoucher.AllowUserToAddRows = False
        dtgReceiptVoucher.AllowUserToDeleteRows = False

        dtgReceiptVoucher.Columns("ID_ReceiptVoucher").Visible = False
        dtgReceiptVoucher.Columns("IsApprove").Visible = False
        dtgReceiptVoucher.Columns("ID_ApproveUser").Visible = False

        With dtgReceiptVoucher.Columns("ReceiptVoucherNo")
            .HeaderText = "SốPT"
            .Width = 50
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
        With dtgReceiptVoucher.Columns("CreatedDate")
            .HeaderText = "Ngày"
            .Width = 90
            .DefaultCellStyle.Format = "dd/MM/yyyy"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
        With dtgReceiptVoucher.Columns("Amount")
            .HeaderText = "Số tiền"
            .Width = 100
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        End With
        With dtgReceiptVoucher.Columns("Description")
            .HeaderText = "Nội dung thu"
            .Width = 270
        End With
        With dtgReceiptVoucher.Columns("Payee")
            .HeaderText = "Người nộp tiền"
            .Width = 140
        End With
        With dtgReceiptVoucher.Columns("PayDay")
            .HeaderText = "Ngày TT"
            .Width = 90
            .DefaultCellStyle.Format = "dd/MM/yyyy"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
    End Sub

    Public Sub KhoiTaoPaymentVoucher(ByVal ID_File As Integer)
        Try
            Dim listID_PV = (From p In db.tblFileFees
                     Where p.ID_File = ID_File
                     Select p.ID_PaymentVoucher).Distinct.ToList

            Dim listPV = From p In db.tblPaymentVouchers
                         Where listID_PV.Contains(p.ID_PaymentVoucher)
                         Select p.ID_PaymentVoucher, p.PaymentVoucherNo, p.CreatedDate, p.Amount, p.Description, p.Reciever, p.IsApprove, p.ID_ApproveUser

            Dim source As New BindingSource
            source.DataSource = listPV
            BindingNavigatorPayment.BindingSource = source
            dtgPaymentVoucher.DataSource = source

            FormatdtgPaymentVoucher()
        Catch ex As Exception

        End Try
    End Sub

    Public Sub FormatdtgPaymentVoucher()
        dtgPaymentVoucher.RowHeadersWidth = 20
        dtgPaymentVoucher.RowHeadersDefaultCellStyle.ForeColor = Color.Blue
        'dtgPaymentVoucher.RowTemplate.Height = 25
        'dtgPaymentVoucher.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
        dtgPaymentVoucher.MultiSelect = False
        'dtgPaymentVoucher.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        dtgPaymentVoucher.ReadOnly = True
        dtgPaymentVoucher.BackgroundColor = Color.White
        dtgPaymentVoucher.AllowUserToAddRows = False
        dtgPaymentVoucher.AllowUserToDeleteRows = False

        dtgPaymentVoucher.Columns("ID_PaymentVoucher").Visible = False
        dtgPaymentVoucher.Columns("IsApprove").Visible = False
        dtgPaymentVoucher.Columns("ID_ApproveUser").Visible = False
        With dtgPaymentVoucher.Columns("PaymentVoucherNo")
            .HeaderText = "SốPC"
            .Width = 50
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
        With dtgPaymentVoucher.Columns("CreatedDate")
            .HeaderText = "Ngày"
            .Width = 90
            .DefaultCellStyle.Format = "dd/MM/yyyy"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With
        With dtgPaymentVoucher.Columns("Amount")
            .HeaderText = "Số tiền"
            .Width = 100
            .DefaultCellStyle.Format = "#,###"
            .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        End With
        With dtgPaymentVoucher.Columns("Description")
            .HeaderText = "Nội dung chi"
            .Width = 270
        End With
        With dtgPaymentVoucher.Columns("Reciever")
            .HeaderText = "Người nhận tiền"
            .Width = 140
        End With
    End Sub

    Private Sub ĐãXácNhậnToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ĐãXácNhậnToolStripMenuItem.Click
        'Kiểm tra phiếu đã xác nhận chưa
        If dtgReceiptVoucher.CurrentRow.Cells("IsApprove").Value = False Then
            Dim ID_ReceiptVoucher As Integer
            ID_ReceiptVoucher = dtgReceiptVoucher.CurrentRow.Cells("ID_ReceiptVoucher").Value
            Dim m_RV As New CReceiptVoucher
            Dim obj_RV As New tblReceiptVoucher
            obj_RV.ID_ReceiptVoucher = ID_ReceiptVoucher
            obj_RV.IsApprove = True
            obj_RV.ID_ApproveUser = m_ID_User
            m_RV.UpdateApprove(obj_RV)

            'Khoi tao dtgReceiptVoucher
            Dim ID_File As Integer = dtgFile.CurrentRow.Cells("ID_File").Value
            KhoiTaoReceiptVoucher(ID_File)
        End If
    End Sub

    Private Sub HủyXácNhậnToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles HủyXácNhậnToolStripMenuItem.Click
        'Kiểm tra có trùng người xác nhận không? Admin, Giám đốc 1, giám đốc 2, ...
        If dtgReceiptVoucher.CurrentRow.Cells("IsApprove").Value = True Then
            If dtgReceiptVoucher.CurrentRow.Cells("Id_ApproveUser").Value = m_ID_User Then
                Dim ID_ReceiptVoucher As Integer
                ID_ReceiptVoucher = dtgReceiptVoucher.CurrentRow.Cells("ID_ReceiptVoucher").Value
                Dim m_RV As New CReceiptVoucher
                Dim obj_RV As New tblReceiptVoucher
                obj_RV.ID_ReceiptVoucher = ID_ReceiptVoucher
                obj_RV.IsApprove = False
                obj_RV.ID_ApproveUser = Nothing
                m_RV.UpdateApprove(obj_RV)

                'Khoi tao dtgReceiptVoucher
                Dim ID_File As Integer = dtgFile.CurrentRow.Cells("ID_File").Value
                KhoiTaoReceiptVoucher(ID_File)
            Else
                MsgBox("Bạn không phải là người xác nhận phiếu")
            End If
        End If
    End Sub

    Private Sub ToolStripMenuItemXacNhanAdvance_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemXacNhanAdvance.Click
        'Kiểm tra phiếu đã xác nhận chưa
        If dtgAdvanceVoucher.CurrentRow.Cells("IsApprove").Value = False Then
            Dim ID_AdvanceVoucher As Integer
            ID_AdvanceVoucher = dtgAdvanceVoucher.CurrentRow.Cells("ID_AdvanceVoucher").Value

            Dim AV = (From p In db.tblAdvanceVouchers
                     Where p.ID_AdvanceVoucher = ID_AdvanceVoucher
                     Select p).SingleOrDefault

            AV.IsApprove = True
            AV.ID_ApproveUser = m_ID_User
            db.SubmitChanges()

            'Khoi tao dtgReceiptVoucher
            Dim ID_File As Integer = dtgFile.CurrentRow.Cells("ID_File").Value
            KhoiTaoAdvanceVoucher(ID_File)
        End If
    End Sub

    Private Sub ToolStripMenuItemHuyXacNhanAdvance_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemHuyXacNhanAdvance.Click
        'Kiểm tra có trùng người xác nhận không? Admin, Giám đốc 1, giám đốc 2, ...
        If dtgAdvanceVoucher.CurrentRow.Cells("IsApprove").Value = True Then
            If dtgAdvanceVoucher.CurrentRow.Cells("Id_ApproveUser").Value = m_ID_User Then
                Dim ID_AdvanceVoucher As Integer
                ID_AdvanceVoucher = dtgAdvanceVoucher.CurrentRow.Cells("ID_AdvanceVoucher").Value

                Dim AV = (From p In db.tblAdvanceVouchers
                     Where p.ID_AdvanceVoucher = ID_AdvanceVoucher
                     Select p).SingleOrDefault

                AV.IsApprove = False
                AV.ID_ApproveUser = Nothing
                db.SubmitChanges()

                'Khoi tao dtgAdvanceVoucher
                Dim ID_File As Integer = dtgFile.CurrentRow.Cells("ID_File").Value
                KhoiTaoAdvanceVoucher(ID_File)
            Else
                MsgBox("Bạn không phải là người xác nhận phiếu")
            End If
        End If
    End Sub

    Private Sub ToolStripMenuItemXacNhanPayment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemXacNhanPayment.Click
        'Kiểm tra phiếu đã xác nhận chưa
        If dtgPaymentVoucher.CurrentRow.Cells("IsApprove").Value = False Then
            Dim ID_PaymentVoucher As Integer
            ID_PaymentVoucher = dtgPaymentVoucher.CurrentRow.Cells("ID_PaymentVoucher").Value

            Dim PV = (From p In db.tblPaymentVouchers
                     Where p.ID_PaymentVoucher = ID_PaymentVoucher
                     Select p).SingleOrDefault

            PV.IsApprove = True
            PV.ID_ApproveUser = m_ID_User
            db.SubmitChanges()

            'Khoi tao dtgPaymentVoucher
            Dim ID_File As Integer = dtgFile.CurrentRow.Cells("ID_File").Value
            KhoiTaoPaymentVoucher(ID_File)
        End If
    End Sub

    Private Sub ToolStripMenuItemHuyXacNhanPayment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemHuyXacNhanPayment.Click
        'Kiểm tra có trùng người xác nhận không? Admin, Giám đốc 1, giám đốc 2, ...
        If dtgPaymentVoucher.CurrentRow.Cells("IsApprove").Value = True Then
            If dtgPaymentVoucher.CurrentRow.Cells("Id_ApproveUser").Value = m_ID_User Then
                Dim ID_PaymentVoucher As Integer
                ID_PaymentVoucher = dtgPaymentVoucher.CurrentRow.Cells("ID_PaymentVoucher").Value

                Dim PV = (From p In db.tblPaymentVouchers
                     Where p.ID_PaymentVoucher = ID_PaymentVoucher
                     Select p).SingleOrDefault

                PV.IsApprove = False
                PV.ID_ApproveUser = Nothing
                db.SubmitChanges()

                'Khoi tao dtgAdvanceVoucher
                Dim ID_File As Integer = dtgFile.CurrentRow.Cells("ID_File").Value
                KhoiTaoPaymentVoucher(ID_File)
            Else
                MsgBox("Bạn không phải là người xác nhận phiếu")
            End If
        End If
    End Sub

    Private Sub dtgPaymentVoucher_DataBindingComplete(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewBindingCompleteEventArgs) Handles dtgPaymentVoucher.DataBindingComplete
        Try
            For i As Integer = 0 To dtgPaymentVoucher.RowCount - 1
                If dtgPaymentVoucher.Rows(i).Cells("IsApprove").Value = True Then
                    dtgPaymentVoucher.Rows(i).DefaultCellStyle.ForeColor = Color.Green
                Else
                    dtgPaymentVoucher.Rows(i).DefaultCellStyle.ForeColor = Color.Red
                End If
            Next

        Catch ex As Exception

        End Try
    End Sub

    Private Sub dtgAdvanceVoucher_DataBindingComplete(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewBindingCompleteEventArgs) Handles dtgAdvanceVoucher.DataBindingComplete
        Try
            For i As Integer = 0 To dtgAdvanceVoucher.RowCount - 1
                'Kiểm tra đã lập phiếu thu tạm ứng chưa
                If dtgAdvanceVoucher.Rows(i).Cells("IsApprove").Value = True Then
                    dtgAdvanceVoucher.Rows(i).DefaultCellStyle.ForeColor = Color.Green
                Else
                    'Phiếu chưa xác nhận
                    dtgAdvanceVoucher.Rows(i).DefaultCellStyle.ForeColor = Color.Red
                    If dtgAdvanceVoucher.Rows(i).Cells("ID_ReceiptVoucher").Value Is Nothing Then
                        'Phiếu tạm ứng chưa lập phiếu thu
                        dtgAdvanceVoucher.Rows(i).DefaultCellStyle.ForeColor = Color.DarkRed
                    End If
                End If
            Next

        Catch ex As Exception

        End Try
    End Sub

    Private Sub dtgReceiptVoucher_DataBindingComplete(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewBindingCompleteEventArgs) Handles dtgReceiptVoucher.DataBindingComplete
        Try
            For i As Integer = 0 To dtgReceiptVoucher.RowCount - 1
                If dtgReceiptVoucher.Rows(i).Cells("IsApprove").Value = True Then
                    dtgReceiptVoucher.Rows(i).DefaultCellStyle.ForeColor = Color.Green
                Else
                    'Phiếu chưa xác nhận
                    dtgReceiptVoucher.Rows(i).DefaultCellStyle.ForeColor = Color.Red
                    If dtgReceiptVoucher.Rows(i).Cells("PayDay").Value Is Nothing Then
                        'Phiếu chưa xác nhận và thanh toán
                        dtgReceiptVoucher.Rows(i).DefaultCellStyle.ForeColor = Color.DarkRed
                    End If
                End If
            Next
        Catch ex As Exception

        End Try
    End Sub

    Private Sub dtgHBL_DataBindingComplete(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewBindingCompleteEventArgs) Handles dtgHBL.DataBindingComplete
        Try
            For i As Integer = 0 To dtgHBL.RowCount - 1
                Dim ID_HBL As Integer
                ID_HBL = dtgHBL.Rows(i).Cells("ID_HBL").Value
                'Kiểm tra đã lập phiếu thu HBL chưa
                Dim c_SellingFee = (From p In db.tblSellingFees
                                   Where p.ID_HBL = ID_HBL And p.ID_ReceiptVoucher IsNot Nothing
                                   Select p).Count

                If c_SellingFee > 0 Then
                    'Đã lập phiếu thu HBL
                    dtgHBL.Rows(i).DefaultCellStyle.ForeColor = Color.Green
                Else
                    'Chưa lập phiếu thu HBL
                    dtgHBL.Rows(i).DefaultCellStyle.ForeColor = Color.Red
                End If
            Next
        Catch ex As Exception

        End Try
    End Sub

    Private Sub dtgFile_DataBindingComplete(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewBindingCompleteEventArgs) Handles dtgFile.DataBindingComplete
        Try
            For i As Integer = 0 To dtgFile.RowCount - 1
                Dim flag As Boolean = True

                Dim buying As Double = 0
                Dim payment As Double = 0
                buying = dtgFile.Rows(i).Cells("TotalBuying").Value
                payment = dtgFile.Rows(i).Cells("TotalPayment").Value

                Dim receipt As Double = 0
                Dim selling As Double = 0
                selling = dtgFile.Rows(i).Cells("TotalSelling").Value
                receipt = dtgFile.Rows(i).Cells("TotalReceipt").Value

                Dim receiptAdv As Double = 0
                Dim advance As Double = 0
                receiptAdv = dtgFile.Rows(i).Cells("TotalReceiptAdvance").Value
                advance = dtgFile.Rows(i).Cells("TotalAdvance").Value

                If buying <> payment Then
                    flag = False
                End If

                If receipt <> selling Then
                    flag = False
                End If

                If receiptAdv <> advance Then
                    flag = False
                End If

                If Not flag Then
                    dtgFile.Rows(i).DefaultCellStyle.ForeColor = Color.Red
                End If

            Next
        Catch ex As Exception

        End Try





        'Try
        '    For i As Integer = 0 To dtgFile.RowCount - 1
        '        Dim flag As Boolean = True

        '        Dim ID_File As Integer
        '        ID_File = dtgFile.Rows(i).Cells("ID_File").Value
        '        'Set color
        '        'File màu đỏ khi 
        '        '1. chưa lập đủ phiếu thu HBL
        '        Dim HBLlist = From p In db.tblHBLs
        '                      Where p.tblFileMBL.ID_File = ID_File
        '                      Select p

        '        For Each HBL In HBLlist
        '            Dim ID_HBL As Integer = HBL.ID_HBL
        '            Dim c_HBL_RV = (From p In db.tblSellingFees
        '                           Where p.ID_HBL = ID_HBL And p.ID_ReceiptVoucher IsNot Nothing
        '                           Select p).Count
        '            If c_HBL_RV = 0 Then
        '                flag = False
        '                Exit For
        '            End If
        '        Next
        '        '2. phiếu tạm ứng chưa lập phiếu thu hoặc chưa xác nhận hết (hoặc chưa tạm ứng hết phí???)
        '        Dim numAV As Integer = 0

        '        If flag Then
        '            Dim listID_AV = (From p In db.tblFileFees
        '                     Where p.ID_File = ID_File
        '                     Select p.ID_AdvanceVoucher).Distinct.ToList

        '            Dim listAV = From p In db.tblAdvanceVouchers
        '                         Where listID_AV.Contains(p.ID_AdvanceVoucher)
        '                         Select p

        '            numAV = listAV.Count

        '            For Each Advance In listAV
        '                If Advance.IsApprove = False Or Advance.ID_ReceiptVoucher Is Nothing Then
        '                    flag = False
        '                    Exit For
        '                End If
        '            Next
        '        End If


        '        '3. phiếu thu chưa thanh toán hoặc chưa xác nhận hết 
        '        'hoặc number(phiếu thu) <> number(HBL) + number(phiếu tạm ứng)
        '        If flag Then
        '            Dim listID_RV As New ArrayList

        '            Dim listID_AV = (From p In db.tblFileFees
        '                         Where p.ID_File = ID_File
        '                         Select p.ID_AdvanceVoucher).Distinct.ToList

        '            Dim listRV_AV = (From p In db.tblAdvanceVouchers
        '                            Where listID_AV.Contains(p.ID_AdvanceVoucher) And p.ID_ReceiptVoucher IsNot Nothing
        '                            Select p.ID_ReceiptVoucher).Distinct.ToList

        '            Dim HBLlist1 = (From p In db.tblHBLs
        '                      Where p.tblFileMBL.ID_File = ID_File
        '                      Select p.ID_HBL).ToList

        '            Dim listRV_HBL = (From p In db.tblSellingFees
        '                         Where HBLlist1.Contains(p.ID_HBL) And p.ID_ReceiptVoucher IsNot Nothing
        '                         Select p.ID_ReceiptVoucher).Distinct.ToList

        '            Dim listRV = From p In db.tblReceiptVouchers
        '                         Where listRV_AV.Contains(p.ID_ReceiptVoucher) Or listRV_HBL.Contains(p.ID_ReceiptVoucher)
        '                         Select p

        '            For Each Receipt In listRV
        '                If Receipt.IsApprove = False Or Receipt.PayDay Is Nothing Then
        '                    flag = False
        '                    Exit For
        '                End If
        '            Next

        '            'number(phiếu thu) <> number(HBL) + number(phiếu tạm ứng)
        '            If listRV.Count <> HBLlist.Count + numAV Then
        '                flag = False
        '            End If
        '        End If


        '        '4. Phiếu chi chưa xác nhận
        '        If flag Then
        '            Dim listID_PV = (From p In db.tblFileFees
        '                         Where p.ID_File = ID_File
        '                         Select p.ID_PaymentVoucher).Distinct.ToList

        '            Dim listPV = From p In db.tblPaymentVouchers
        '                         Where listID_PV.Contains(p.ID_PaymentVoucher)
        '                         Select p

        '            For Each Payment In listPV
        '                If Payment.IsApprove = False Then
        '                    flag = False
        '                    Exit For
        '                End If
        '            Next
        '        End If


        '        'Kết luận
        '        If flag Then
        '            'OK
        '            dtgFile.Rows(i).DefaultCellStyle.ForeColor = Color.Green
        '        Else
        '            'Chưa OK
        '            dtgFile.Rows(i).DefaultCellStyle.ForeColor = Color.Red
        '        End If
        '    Next
        'Catch ex As Exception
        '    MsgBox(ex.Message)
        'End Try
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        KhoiTaoFile(txtFrom.Text, txtTo.Text, chkExport.Checked)
    End Sub
End Class